package Dao;

import DBCPUtil.DBCPUtil;
import Dto.Employee;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class EmployeeDao {
    QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
    //列表 分页
    public List<Employee> selectEmployList(Integer pageNum) throws SQLException {
        String sql = "select * from employee limit ?,?";
        List<Employee> list = qr.query(sql,new BeanListHandler<>(Employee.class),(pageNum-1)*10,10);
        return list;
    }
    //数据条数
    public Long selectEmployeeCount() throws SQLException {
        String sql = "select count(*) from employee";
        Long count = qr.query(sql,new ScalarHandler<>());
        return count;
    }
    //员工添加
    public void addEmployee(Employee employee) throws SQLException {
        String sql = "insert into employee values(null,?,?,?,?,?,?,?)";
        qr.update(sql,employee.getName(),employee.getIdcard(),employee.getGender(),employee.getPhone(),employee.getAddress(),employee.getHiredate(),employee.getStatus());
    }
    //员工预修改
    public Employee preUpdateEmployee(Integer id) throws SQLException {
        String sql = "select * from employee where id = ?";
        Employee employee = qr.query(sql,new BeanHandler<>(Employee.class),id);
        return employee;
    }
    //员工修改
    public void updateEmployee(Employee employee) throws SQLException {
        String sql = "update employee set name=?,idcard=?,gender=?,phone=?,address=?,hiredate=?,status=? where id=?";
        qr.update(sql,employee.getName(),employee.getIdcard(),employee.getGender(),employee.getPhone(),employee.getAddress(),employee.getHiredate(),employee.getStatus(),employee.getId());
    }
    //员工删除
    public void deleteEmployee(Integer id) throws SQLException {
        String sql = "delete from employee where id=?";
        qr.update(sql,id);
    }
    //员工姓名模糊查询
    public List<Employee> selectName(String name) throws SQLException {
        String sql = "select * from employee where name like ?";
        List<Employee> list = qr.query(sql,new BeanListHandler<>(Employee.class),"%"+name+"%");
        return list;
    }
    //身份证号精确匹配
    public List<Employee> selectIdCard(String idcard) throws SQLException {
        String sql = "select * from employee where idcard=?";
        List<Employee> list = qr.query(sql,new BeanListHandler<>(Employee.class),idcard);
        return list;
    }
    //入职日期查询
    public List<Employee> selectHireDate(String startDate,String endDate) throws SQLException {
        String sql = "select * from employee where hiredate between ? and ?";
        List<Employee> list = qr.query(sql,new BeanListHandler<>(Employee.class),startDate,endDate);
        return list;
    }
    //员工状态查询
    public List<Employee> selectStatus(String status) throws SQLException {
        String sql = "select * from employee where status = ?";
        List<Employee> list = qr.query(sql,new BeanListHandler<>(Employee.class),status);
        return list;
    }
    //四种全是空 查出所有数据
    public List<Employee> selectCount() throws SQLException {
        String sql = "select * from employee";
        List<Employee> list = qr.query(sql,new BeanListHandler<>(Employee.class));
        return list;
    }
}
